﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using V5_EaspLibs;
using System.Data;
using V5_EaspLibs.DBUtility;

namespace V5_FrameWork.Data {
    public class BaseModelCategory {
        private string TableName = string.Empty;
        private string DbType = string.Empty;
        private string DbString = string.Empty;
        #region
        private int m_ClassID;
        private string m_ClassName;
        private string m_ClassCode;
        private int m_ParentID;
        /// <summary> 
        /// 
        /// </summary>
        public int ClassID {
            get { return m_ClassID; }
            set { m_ClassID = value; }
        }
        /// <summary> 
        /// 
        /// </summary>
        public string ClassName {
            get { return m_ClassName; }
            set { m_ClassName = value; }
        }

        /// <summary> 
        /// 
        /// </summary>
        public string ClassCode {
            get { return m_ClassCode; }
            set { m_ClassCode = value; }
        }

        /// <summary> 
        /// 
        /// </summary>
        public int ParentID {
            get { return m_ParentID; }
            set { m_ParentID = value; }
        }
        #endregion

        public BaseModelCategory(string _tableName) {
            TableName = _tableName;
        }
        public BaseModelCategory(string _tableName, string dbType, string dbString) {
            TableName = _tableName;
            DbType = dbType;
            DbString = dbString;
        }
        #region

        public int GetMaxId() {
            object obj = Easp.DB.ExecuteScalar("Select max(ClassID) From " + TableName + " ");
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) {
                return 1;
            }
            else {
                return int.Parse("0" + obj) + 1;
            }
        }

        public bool Exists(int ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + TableName + " ");
            strSql.Append(" where ClassID=" + ID);
            return false;
        }
        /// <summary>
        /// 添加分类
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(BaseModelCategory model) {
            string SQLString = string.Empty;
            string leftCode = string.Empty, selfCode = string.Empty, parentCode = string.Empty;
            int result = 0;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("Insert Into " + TableName + "(ClassID,ClassName,ClassCode,ParentID)values(");
            sbSql.Append("" + model.ClassID + ",");
            sbSql.Append("'" + model.ClassName + "',");
            sbSql.Append("'" + model.ClassCode + "',");
            sbSql.Append("" + model.ParentID + "");
            sbSql.Append(")");
            result = Easp.DB.ExecuteNonQuery(sbSql.ToString());
            #region 开始更新 ClassCode
            if (result > 0) {
                if (model.ParentID != 0) {
                    object obj = Easp.DB.ExecuteScalar("Select ClassCode From " + TableName + " Where ClassID=" + model.ParentID);
                    parentCode = Convert.ToString(obj);
                }
                if (Easp.DB.DbType == DataBaseType.SQLite) {
                    SQLString = @"
                select Classcode from [" + TableName + @"] 
                where leftstr(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and length(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc
            ";
                }
                else if (Easp.DB.DbType == DataBaseType.OleDb) {
                    SQLString = @"
                select Classcode from [" + TableName + @"] 
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc
            ";
                }
                else {
                    SQLString = @"
                select Classcode from [" + TableName + @"] 
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc
            ";
                }
                DataTable dt = Easp.DB.ExecuteQuery(SQLString).Tables[0];
                if (dt.Rows.Count > 0) {
                    leftCode = dt.Rows[0][0].ToString();
                }
                if (leftCode.Length > 0) {
                    selfCode = Convert.ToString(Convert.ToInt32(leftCode.Substring(leftCode.Length - 4, 4)) + 1).PadLeft(4, '0');
                }
                else {
                    selfCode = "0001";
                }
                selfCode = parentCode + selfCode;
                result = Easp.DB.ExecuteNonQuery("Update " + TableName + " Set ClassCode='" + selfCode + "' Where ClassID=" + model.ClassID);
            }
            #endregion
            return result;
        }
        /// <summary>
        /// 更新分类
        /// </summary>
        /// <param name="model"></param>
        public int Update(BaseModelCategory model) {
            string SQLString = string.Empty;
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("Update " + TableName + " Set ");
            sbSql.Append("ClassName='" + model.ClassName + "',");
            sbSql.Append("ParentID='" + model.ParentID + "' ");
            sbSql.Append("  Where ClassID=" + model.ClassID + " ");
            int result = Easp.DB.ExecuteNonQuery(sbSql.ToString());
            #region
            string leftCode = string.Empty, selfCode = string.Empty, parentCode = string.Empty, oldCode = string.Empty, sourceCode = string.Empty;

            object parentIDobj = Easp.DB.ExecuteScalar("Select ClassCode From " + TableName + " Where ClassID=" + model.ParentID);
            parentCode = Convert.ToString(parentIDobj);

            object obj = Easp.DB.ExecuteScalar("Select ClassCode From " + TableName + " Where ClassID=" + model.ClassID);
            oldCode = Convert.ToString(obj);
            sourceCode = oldCode;
            oldCode = oldCode.Substring(0, oldCode.Length - 4);
            if (oldCode != parentCode) {
                DataTable dt = Easp.DB.ExecuteQuery(@"
                select Classcode from [" + TableName + @"] 
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc
            ").Tables[0];
                if (dt.Rows.Count > 0) {
                    leftCode = dt.Rows[0][0].ToString();
                }
                if (leftCode.Length > 0) {
                    selfCode = Convert.ToString(Convert.ToInt32(leftCode.Substring(leftCode.Length - 4, 4)) + 1).PadLeft(4, '0');
                }
                else {
                    selfCode = "0001";
                }
                selfCode = parentCode + selfCode;
                result = Easp.DB.ExecuteNonQuery("Update " + TableName + " Set ClassCode='" + selfCode + "' Where ClassID=" + model.ClassID);
                result = Easp.DB.ExecuteNonQuery("update " + TableName + " Set ClassCode='" + selfCode + "'+right(ClassCode,len(ClassCode)-" + sourceCode.Length.ToString() + ") where ClassCode like '" + sourceCode + "%'");
            }
            return result;
            #endregion
        }

        public void Delete(int ID) {
            object obj = Easp.DB.ExecuteNonQuery("Select ClassCode From " + TableName + " Where ClassID=" + ID);
            string cCode = Convert.ToString(obj);
            string strCondition = " classcode like '" + cCode + "%' and len(classcode)>" + cCode.Length;
            bool isDel = this.Exists(strCondition);
            if (isDel) {
                return;
            }
            Easp.DB.ExecuteNonQuery("Delete From " + TableName + " Where ClassID=" + ID);
        }

        #region 获取下拉列表数据

        public DataTable GetDropDownList(int ChannelID, string strMsg) {
            DataTable dt = Easp.DB.ExecuteQuery("select ClassID,ClassName,ClassCode from [" + TableName + "] Order by ClassCode ").Tables[0];
            DataTable dropdt = new DataTable();
            dropdt.Columns.Add("ClassID");
            dropdt.Columns.Add("ClassName");
            if (strMsg.Trim() != "") {
                DataRow dr = dropdt.NewRow();
                dr["ClassID"] = "0";
                dr["ClassName"] = strMsg;
                dropdt.Rows.Add(dr);
            }
            for (int i = 0; i < dt.Rows.Count; i++) {
                DataRow dr = dropdt.NewRow();
                dr["ClassID"] = dt.Rows[i]["ClassID"].ToString();
                dr["ClassName"] = GetColumnListName(dt.Rows[i]["ClassName"].ToString(), dt.Rows[i]["ClassCode"].ToString());
                dropdt.Rows.Add(dr);
            }
            return dropdt;
        }
        /// <summary>
        /// 获得逐级缩进的栏目名
        /// </summary>
        /// <param name="sName">栏目名</param>
        /// <param name="sCode">栏目code</param>
        /// <returns>逐级缩进的栏目名</returns>
        protected string GetColumnListName(string sName, string sCode) {
            int Level = (sCode.Length / 4 - 1) * 2;
            string sStr = sCode.Length == 4 ? "" : "├";
            if (Level > 0) {
                for (int i = 0; i < Level; i++)
                    sStr += "－";
            }
            return sStr + sName;
        }
        #endregion
        /// <summary>
        /// 移动分类
        /// </summary>
        /// <param name="ClassID"></param>
        /// <param name="isup">1降序 其他为升序</param>
        public void MoveClass(int ClassID, int isUp) {
            string oldCode = string.Empty;

            object obj1 = Easp.DB.ExecuteScalar("Select ClassCode From " + TableName + " Where ClassID=" + ClassID);

            oldCode = Convert.ToString("" + obj1);
            string subStr = "substring";
            int codeLen = oldCode.Length;
            if (codeLen > 1) {
                string temp = string.Empty;
                string wStr = "";
                string newStr = "";
                for (int i = 0; i < codeLen; i++)
                    newStr += "-";
                if (codeLen > 4)
                    wStr = " and left(ClassCode," + Convert.ToString(codeLen - 4) + ")='" + oldCode.Substring(0, codeLen - 4) + "'";
                if (isUp == 1)
                    wStr = "select top 1 ClassCode from " + TableName + " where len(ClassCode)=" + codeLen.ToString() + " and ClassCode<'" + oldCode + "'" + wStr + " order by ClassCode desc ";
                else
                    wStr = "select top 1 ClassCode from " + TableName + " where len(ClassCode)=" + codeLen.ToString() + " and ClassCode>'" + oldCode + "'" + wStr + " order by ClassCode ";
                DataTable dt = Easp.DB.ExecuteQuery(wStr).Tables[0];
                if (dt.Rows.Count > 0)
                    temp = dt.Rows[0][0].ToString();
                if (temp.Length > 1) {
                    //Move Under Column
                    wStr = "update " + TableName + " set ClassCode='" + newStr + "'+" + subStr + "(ClassCode," + Convert.ToString(codeLen + 1) + ",len(Classcode)) where left(ClassCode," + codeLen.ToString() + ")='" + temp + "'";
                    Easp.DB.ExecuteNonQuery(wStr);
                    //Update Target Column
                    wStr = "update " + TableName + " set classcode='" + temp + "'+" + subStr + "(classcode," + Convert.ToString(codeLen + 1) + ",len(Classcode)) where left(classcode," + codeLen.ToString() + ")='" + oldCode + "'";
                    Easp.DB.ExecuteNonQuery(wStr);
                    //Update Under Column
                    wStr = "update " + TableName + " set classcode='" + oldCode + "'+" + subStr + "(classcode," + Convert.ToString(newStr.Length + 1) + ",len(classcode)) where left(Classcode," + newStr.Length.ToString() + ")='" + newStr + "'";
                    Easp.DB.ExecuteNonQuery(wStr);
                    //以下是更新栏目信息 可以不用添加
                }
            }
        }

        public bool Exists(string strWhere) {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select count(1) from " + TableName + "");
            if (strWhere.Trim() != "") {
                sb.Append(" Where " + strWhere);
            }
            return Easp.DB.ExecuteScalar(sb.ToString()) == null ? true : false;
        }
        #endregion
    }
}
